package com.simple.common.utils.ExcelUtil;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * @Description 根据模版导出Excel程序
 */
public class ExcelTemplateProc {

    /**
     * @param templateFileName
     * @param exportFilePathAndName
     * @param staticDataMap
     * @param dynamicDataMappingList
     * @return void
     * @description: 根据模版导出Excel入口
     */
    public static void doExportExcelByTemplateProc(String templateFileName, String exportFilePathAndName,
                                                   Map<String, Object> staticDataMap,
                                                   List<DynamicDataMapping> dynamicDataMappingList) throws IOException {
        /**
         * 1. 从resources下加载模板并替换
         *    使用 ResourceUtils 加载文件
         */
//        Resource resource = new ClassPathResource("template/inuModel.inu");
//        File inuModel = new File(filePath);
        File file = ResourceUtils.getFile(templateFileName);
        InputStream inputStream = new FileInputStream(file);

        Workbook workbook = dealFirstSheetByTemplate(inputStream, staticDataMap, dynamicDataMappingList);
        // 2. 保存到本地
        saveExportFile(workbook, exportFilePathAndName);
    }

    /**
     * @param workbook
     * @param excelFilePath
     * @return void
     * @description: 保存导出的Excel文件到服务器
     */
    public static void saveExportFile(Workbook workbook, String excelFilePath) throws IOException {
        FileOutputStream outputStream = new FileOutputStream(excelFilePath);
        executeWorkBookWrite(workbook, outputStream);
    }

    /**
     * @param workbook
     * @param outputStream
     * @return void
     * @description: 数据输出
     */
    public static void executeWorkBookWrite(Workbook workbook, OutputStream outputStream) throws IOException {
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        workbook.close();
    }

    /**
     * @param inputStream
     * @param staticDataMap
     * @param dynamicDataMappingList
     * @return org.apache.poi.ss.usermodel.Workbook
     * @description: 处理只有一个sheet页的模版
     */
    public static Workbook dealFirstSheetByTemplate(InputStream inputStream,
                                                    Map<String, Object> staticDataMap,
                                                    List<DynamicDataMapping> dynamicDataMappingList) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 按模板处理sheet页
        dealSheetDataByTemplate(sheet, staticDataMap, dynamicDataMappingList);
        return workbook;
    }

    /**
     * @param sheet
     * @param staticDataMap
     * @param dynamicDataMappingList
     * @return void
     * @description: 按模板处理sheet页里的数据
     */
    private static void dealSheetDataByTemplate(XSSFSheet sheet, Map<String, Object> staticDataMap, List<DynamicDataMapping> dynamicDataMappingList) {
        // 循环sheet里每一行
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            DynamicDataMapping dynamicDataMapping = getDynamicRowDataByMatch(row, dynamicDataMappingList);
            if (dynamicDataMapping != null) {
                i = getTemplateLastRowIndexAfterDealTemplate(sheet, i, dynamicDataMapping);
            } else {
                dealTemplateDataRow(row, null, staticDataMap);
            }
        }
    }

    /**
     * @param row
     * @param dataMap
     * @param dataPrefix
     * @return void
     * @description: 循环处理模版中每行的数据
     */
    private static void dealTemplateDataRow(XSSFRow row, String dataPrefix, Map<String, Object> dataMap) {
        if (dataMap == null) {
            return;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            XSSFCell cell = row.getCell(i);
            fillInTemplateCellDataValue(cell, dataPrefix, dataMap);
        }
    }

    /**
     * @param cell
     * @param dataPrefix
     * @param dataMap
     * @return void
     * @description: 填充模版里单元格的值
     */
    private static void fillInTemplateCellDataValue(XSSFCell cell, String dataPrefix, Map<String, Object> dataMap) {
        if (cell == null) {
            return;
        }
        String cellValue = cell.getStringCellValue();//获取模版里设置的数据
        if (StringUtils.isEmpty(cellValue)) {
            return;
        }
        boolean flag = false;
        dataPrefix = StringUtils.isEmpty(dataPrefix) ? "" : (dataPrefix + ".");
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            // 循环所有，因为可能一行有多个占位符
            String cellTemplateStr = "{{" + dataPrefix + entry.getKey() + "}}";
            if (cellValue.contains(cellTemplateStr)) {
                // 替换模版中单元格的数据
                cellValue = cellValue.replace(cellTemplateStr, entry.getValue() == null ? "" : entry.getValue().toString());
                flag = true;
            }
        }
        if (flag) {
            cell.setCellValue(cellValue);
        }
    }

    /**
     * @param row
     * @param dynamicDataMappingList
     * @return com.liu.susu.excel.template.poi.common.DynamicDataMapping
     * @description: 通过模版sheet中的行数据 与 动态数据匹配,获取此行需要填充的动态数据
     */
    private static DynamicDataMapping getDynamicRowDataByMatch(XSSFRow row, List<DynamicDataMapping> dynamicDataMappingList) {
        if (dynamicDataMappingList == null || dynamicDataMappingList.size() < 1) {
            return null;
        }
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            XSSFCell cell = row.getCell(j);
            String value = cell.getStringCellValue();
            if (value != null) {
                for (DynamicDataMapping dynamicData : dynamicDataMappingList) {
                    if (value.startsWith("{{" + dynamicData.getDataId() + ".")) {
                        return dynamicData;
                    }
                }
            }
        }
        return null;
    }

    /**
     * @param sheet
     * @param rowIndex
     * @param dynamicDataMapping
     * @return int
     * @description: 根据动态数据的条数动态复制模版行，每处理一个类型的list返回最后的行数，进而处理下一个类型的list
     */
    private static int getTemplateLastRowIndexAfterDealTemplate(XSSFSheet sheet, int rowIndex, DynamicDataMapping dynamicDataMapping) {
        if (dynamicDataMapping == null) {
            return rowIndex;
        }
        int dataRows = dynamicDataMapping.getDataList().size();
        // 需要拷贝的行数（因为模板行本身占1行，所以-1）
        int copyRows = dataRows - 1;
        if (copyRows > 0) {
            /**
             * shiftRows: 从动态数据模版行(rowIndex)到最后一行，这些全部行都向下移copyRows行
             *            相当于模版行上面插入n行空行（n=copyRows）
             */
            sheet.shiftRows(rowIndex, sheet.getLastRowNum(), copyRows, true, false);
            // 拷贝策略
            CellCopyPolicy cellCopyPolicy = makeCellCopyPolicy();
            // 因为从模版行开始向下平移了copyRows行，所以这里 模板行=rowIndex + copyRows,
            int templateDataRow = rowIndex + copyRows;
            // 因为模版行上新增了空行，所以要把模板所在行的模版 拷贝到上面新增的空行
            for (int i = 0; i < copyRows; i++) {
                //templateDataRow-模版行数据   rowIndex + i循环的当前空行
                sheet.copyRows(templateDataRow, templateDataRow, rowIndex + i, cellCopyPolicy);
            }
        }
        // 循环模版行：动态替换模版行（将模版行里的模版替换成动态数据）
        for (int j = rowIndex; j < rowIndex + dataRows; j++) {
            Map<String, Object> dataMap = dynamicDataMapping.getDataList().get(j - rowIndex);
            dealTemplateDataRow(sheet.getRow(j), dynamicDataMapping.getDataId(), dataMap);
        }
        return rowIndex + copyRows;
    }

    /**
     * @param
     * @return org.apache.poi.ss.usermodel.CellCopyPolicy
     * @description: 拷贝策略
     */
    public static CellCopyPolicy makeCellCopyPolicy() {
        CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
        cellCopyPolicy.setCopyCellValue(true);
        cellCopyPolicy.setCopyCellStyle(true);
        return cellCopyPolicy;
    }


}


